########################################################################
#  Pyfm - personal finance data-base
#  Copyright (C) 2007,8 Ehud Ben-Reuven
#  udi@benreuven.com
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation version 2.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
########################################################################
"""Convert Citi N.A. (Citibank North America)  Portfolio (investment) Account Activity CSV file to flat lines.
"""
from csv2flt import *
from obj.flat import stagvalue
import re
import logging

header = [
['Account Number','Memo','Price','Quantity','Activity Type','Check Number','Total','Trade Date','Cusip']
]
line=['(?P<ACCTID>[\d\-A-Z]+)',
      strre('MEMO'),
	  floatre('UNITPRICE'),
      floatre('UNITS'),
      strre('TRNTYPE'),
	  "(99999)?",
	  floatre('TOTAL'),
      '(?P<month>\d{2})/(?P<day>\d{2})/(?P<year>\d{4})',
      '#(((?P<SECID>\d{9})000)|\s*)',
      ]
footer=[ # This footer was added between 070501 and 070601
	[],
["<!-- -->"],
	]

def path(line):
	return ['CitiNA','portfolio','activity']

pre=None

def flat(csvfile,fout,linetxt):
	h,t,f = readcsvtable(csvfile,header,line,footer,optional_footer=True)

	accttype='INVESTMENT'
	currency='USD'

	table_total=0
	accounts=[]
	for l in t:
		acctid=l['ACCTID']
		memo=l['MEMO']
		if l['SECID']:
			secid='CUSIP='+l['SECID']
		else:
			secid='CURRENT=CASH='+currency
			acctid='INVCASH'+acctid
		dttrade = l['year']+l['month']+l['day']
		units=float(l['UNITS']) if l['UNITS'] else None
		if units==0: units=None
		unitprice=float(l['UNITPRICE']) if l['UNITPRICE'] else None	
		if unitprice==0: unitprice=None
		total=float(l['TOTAL'])	if l['TOTAL'] else None
		if total==0: total=None

		# TRNTYPE
		relsecid=None
		assetclass=None
		trntype=l['TRNTYPE']
		if secid.startswith('CURRENT='):
			if trntype=='DEPOSIT':
				assert unitprice==None
				assert units==None
				assert total>0
				trntype='CREDIT'
			elif trntype=='CASH JRNL':
				assert unitprice==None
				assert units==None
				assert total<0
				trntype='DEBIT'
			else:
				raise Exception('Unknown TRNTYPE')
		else:
			if trntype=='BOUGHT':
				assert unitprice>0
				assert units>0
				assert units*unitprice<=-total+0.02 # rounding error
				trntype='BUY'
			elif trntype=='SOLD':
				assert unitprice>0
				assert units<0
				assert -units*unitprice>=total-0.01 # Rounding error
				trntype='SELL'
			elif trntype=='INTEREST':
				assert unitprice==None or unitprice==1 # interest from deposit
				assert units==None
				assert total>0
				trntype='INT'				
			elif trntype=='DIVIDEND':
				assert unitprice==None
				assert units==None
				assert total
				if total<0:
					logging.warning('%s','Negative dividend %f'%total)
				trntype='DIV'
			elif trntype=='CAPITAL GAIN':
				assert unitprice==None
				assert units==None
				assert total
				if total<0:
					logging.warning('%s','Negative dividend %f'%total)
				if memo.find('L/T')!=-1:
					trntype='CGLONG'
				else:
					trntype='CGSHORT'
			elif trntype=='REINVEST':
				if unitprice==None and units==None:
					assert total<0
					relsecid=secid
					trntype='DEBIT'
					secid='CURRENT=CASH='+currency
					acctid='INVCASH'+acctid
				else:
					assert unitprice>0
					assert units>0
					assert not total # is 0 because it is always followed with a REINVEST transaction in current account
					trntype='BUY'
			elif trntype=='REVERSAL':
				pass
			elif trntype=='CONVERSION':
				assert total==None
				assert unitprice==None
				trntype='TRANSFER'
			elif trntype=='WITHDRAWAL':
				if units:
					assert unitprice==1
					assert units<0
					assert -units*unitprice>=total-0.01 # Rounding error
				trntype='DEBIT'
				assetclass='DEPOSIT'
			elif trntype=='DEPOSIT':
				if units:
					assert unitprice==1
					assert units>0
					assert units*unitprice<=-total+0.02 # rounding error
				trntype='CREDIT'
				assetclass='DEPOSIT'
			else:
				raise Exception('Unknown TRNTYPE')
				
				
		
		print >>fout,stagvalue('ACCTID',acctid),stagvalue('ACCTTYPE','INVESTMENT'),
		print >>fout,stagvalue('SECID',secid),
		print >>fout,stagvalue('DTSETTLE',dttrade),
		print >>fout,stagvalue('DTTRADE',dttrade),
		print >>fout,stagvalue('TRNTYPE',trntype),
		print >>fout,stagvalue('UNITS',units),
		print >>fout,stagvalue('UNITPRICE',unitprice),
		print >>fout,stagvalue('TOTAL',total),
		print >>fout,stagvalue('CURRENCY',currency),
		print >>fout,stagvalue('ASSETCLASS',assetclass),
		print >>fout,stagvalue('RELSECID',relsecid),
		print >>fout,linetxt
		
		# Every transaction generates a matching transaction in the current account.
		# The only exception is a REINVEST transaction that for some reason has a TOTAL=0 in the CSV file but on the other hand
		# has a seperatet DEBIT transaction in the same CSV file.
		if total and not secid.startswith('CURRENT=CASH='):
			relsecid=secid
			trntype='CREDIT' if total>0 else 'DEBIT'
			secid='CURRENT=CASH='+currency
			acctid='INVCASH'+acctid
			print >>fout,stagvalue('ACCTID',acctid),stagvalue('ACCTTYPE','INVESTMENT'),
			print >>fout,stagvalue('SECID',secid),
			print >>fout,stagvalue('DTSETTLE',dttrade),
			print >>fout,stagvalue('DTTRADE',dttrade),
			print >>fout,stagvalue('TRNTYPE',trntype),
			print >>fout,stagvalue('UNITS',total),
			print >>fout,stagvalue('UNITPRICE',1.),
			print >>fout,stagvalue('TOTAL',total),
			print >>fout,stagvalue('CURRENCY',currency),
			print >>fout,stagvalue('RELSECID',relsecid),
			print >>fout,linetxt

help = """Convert Citi N.A. (Citibank North America)  Portfolio (investment) Account Activity CSV file to flat lines.
The CSV files should be located at $PYFM_DATA/imports/CitiNA/portfolio/activity/
and converted to flat file at  $PYFM_DATA/flt/CitiNA/portfolio/activity/
conversions are not perfromed if the files already exists
Use -FORCE=1 to force the convertion
"""

ext='*.csv'
